﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace ebay.Dal
{
    public class PayPalTransactionDetailsHelper
    {
        public static DataTable Query()
        {
            string sql = "SELECT eBayUserId, PayPalTransactionID FROM EbaySelling WHERE PayPalDownload = 0 AND PayPalTransactionID > ''";

            DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.ceterDB_Conn, CommandType.Text, sql, null);

            return table;
        }

        public static bool WriteServer(List<ebay.Model.PayPalTransactionDetails> listDetails)
        {
            foreach (ebay.Model.PayPalTransactionDetails details in listDetails)
            {
                SqlParameter[] parameters = new SqlParameter[67];
                
                parameters[0] = new SqlParameter("@TimeStamp", SqlDbType.DateTime, 1);
                parameters[0].Value = details.OrderTime;

                parameters[1] = new SqlParameter("@TimeZone", SqlDbType.VarChar, 10);
                parameters[1].Value = "GMT";

                parameters[2] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
                parameters[2].Value = details.FirstName + " " + details.LastName;

                parameters[3] = new SqlParameter("@Email", SqlDbType.VarChar, 255);
                parameters[3].Value = details.Email;

                parameters[4] = new SqlParameter("@Status", SqlDbType.VarChar, 25);
                parameters[4].Value = details.PaymentStatus;

                parameters[5] = new SqlParameter("@TransactionID", SqlDbType.VarChar, 20);
                parameters[5].Value = details.TransactionID;

                parameters[6] = new SqlParameter("@TransactionType", SqlDbType.VarChar, 50);
                parameters[6].Value = details.PaymentType;

                parameters[7] = new SqlParameter("@AMT", SqlDbType.Float);
                parameters[7].Value = details.AMT;

                parameters[8] = new SqlParameter("@FeeAMT", SqlDbType.Float);
                parameters[8].Value = details.FeeAMT;

                parameters[9] = new SqlParameter("@NetAMT", SqlDbType.Float);
                parameters[9].Value = details.AMT + details.FeeAMT;

                parameters[10] = new SqlParameter("@ShippingCalculationMode", SqlDbType.VarChar, 50);
                parameters[10].Value = details.ShippingCalculationMode;

                parameters[11] = new SqlParameter("@InsuranceOptionSelected", SqlDbType.VarChar, 50);
                parameters[11].Value = details.InsuranceOptionSelected;

                parameters[12] = new SqlParameter("@GiftMessage", SqlDbType.VarChar, 50);
                parameters[12].Value = details.GiftMessage;

                parameters[13] = new SqlParameter("@GiftReceiptenable", SqlDbType.VarChar, 50);
                parameters[13].Value = details.GiftReceiptenable;

                parameters[14] = new SqlParameter("@GiftWrapName", SqlDbType.VarChar, 50);
                parameters[14].Value = details.GiftWrapName;

                parameters[15] = new SqlParameter("@BuyerMarketingEmail", SqlDbType.VarChar, 50);
                parameters[15].Value = details.BuyerMarketingEmail;

                parameters[16] = new SqlParameter("@SurveyQuestion", SqlDbType.VarChar, 50);
                parameters[16].Value = details.SurveyQuestion;

                parameters[17] = new SqlParameter("@ReceiverBusiness", SqlDbType.VarChar, 255);
                parameters[17].Value = details.ReceiverBusiness;

                parameters[18] = new SqlParameter("@ReceiverEmail", SqlDbType.VarChar, 255);
                parameters[18].Value = details.ReceiverEmail;

                parameters[19] = new SqlParameter("@ReceiverID", SqlDbType.VarChar, 50);
                parameters[19].Value = details.ReceiverID;

                parameters[20] = new SqlParameter("@DetailTransactionType", SqlDbType.VarChar, 50);
                parameters[20].Value = details.TransactionType;

                parameters[21] = new SqlParameter("@PayerID", SqlDbType.VarChar, 50);
                parameters[21].Value = details.PayerID;

                parameters[22] = new SqlParameter("@PayerStatus", SqlDbType.VarChar, 50);
                parameters[22].Value = details.PayerStatus;

                parameters[23] = new SqlParameter("@CountryCode", SqlDbType.VarChar, 25);
                parameters[23].Value = details.CountryCode;

                parameters[24] = new SqlParameter("@PayerBusiness", SqlDbType.VarChar, 50);
                parameters[24].Value = details.PayerBusiness;

                parameters[25] = new SqlParameter("@Salutation", SqlDbType.VarChar, 50);
                parameters[25].Value = details.Salutation;

                parameters[26] = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50);
                parameters[26].Value = details.FirstName;

                parameters[27] = new SqlParameter("@MiddleName", SqlDbType.NVarChar, 50);
                parameters[27].Value = details.MiddleName;

                parameters[28] = new SqlParameter("@LastName", SqlDbType.NVarChar, 50);
                parameters[28].Value = details.LastName;

                parameters[29] = new SqlParameter("@Suffix", SqlDbType.VarChar, 50);
                parameters[29].Value = details.Suffix;

                parameters[30] = new SqlParameter("@AddressOwner", SqlDbType.VarChar, 50);
                parameters[30].Value = details.AddressOwner;

                parameters[31] = new SqlParameter("@AddressStatus", SqlDbType.VarChar, 50);
                parameters[31].Value = details.AddressStatus;

                parameters[32] = new SqlParameter("@ShipToName", SqlDbType.NVarChar, 50);
                parameters[32].Value = details.ShipToName;

                parameters[33] = new SqlParameter("@ShipToStreet", SqlDbType.NVarChar, 500);
                parameters[33].Value = details.ShipToStreet;

                parameters[34] = new SqlParameter("@ShipToStreet2", SqlDbType.NVarChar, 500);
                parameters[34].Value = details.ShipToStreet2;

                parameters[35] = new SqlParameter("@ShipToCity", SqlDbType.NVarChar, 50);
                parameters[35].Value = details.ShipToCity;

                parameters[36] = new SqlParameter("@ShipToState", SqlDbType.NVarChar, 50);
                parameters[36].Value = details.ShipToState;

                parameters[37] = new SqlParameter("@ShipToZip", SqlDbType.VarChar, 50);
                parameters[37].Value = details.ShipToZip;

                parameters[38] = new SqlParameter("@ShipToCountryCode", SqlDbType.VarChar, 50);
                parameters[38].Value = details.ShipToCountryCode;

                parameters[39] = new SqlParameter("@ShipToPhonenum", SqlDbType.VarChar, 50);
                parameters[39].Value = details.ShipToPhonenum;

                parameters[40] = new SqlParameter("@ParentTransactionID", SqlDbType.VarChar, 25);
                parameters[40].Value = details.ParentTransactionID;

                parameters[41] = new SqlParameter("@ReceiptID", SqlDbType.VarChar, 25);
                parameters[41].Value = details.ReceiptID;

                parameters[42] = new SqlParameter("@PaymentType", SqlDbType.VarChar, 50);
                parameters[42].Value = details.PaymentType;

                parameters[43] = new SqlParameter("@OrderTime", SqlDbType.DateTime, 50);
                parameters[43].Value = details.OrderTime;

                parameters[44] = new SqlParameter("@CurrencyCode", SqlDbType.VarChar, 50);
                parameters[44].Value = details.CurrencyCode;

                parameters[45] = new SqlParameter("@SettleAMT", SqlDbType.VarChar, 50);
                parameters[45].Value = details.SettleAMT;

                parameters[46] = new SqlParameter("@TaxAMT", SqlDbType.VarChar, 50);
                parameters[46].Value = details.ExchangeRate;

                parameters[47] = new SqlParameter("@PaymentStatus", SqlDbType.VarChar, 50);
                parameters[47].Value = details.PaymentStatus;

                parameters[48] = new SqlParameter("@PendingReason", SqlDbType.VarChar, 50);
                parameters[48].Value = details.PendingReason;

                parameters[49] = new SqlParameter("@ReasonCode", SqlDbType.VarChar, 50);
                parameters[49].Value = details.ReasonCode;

                parameters[50] = new SqlParameter("@ProtectionEligibility", SqlDbType.VarChar, 50);
                parameters[50].Value = details.ProtectionEligibility;

                parameters[51] = new SqlParameter("@ProtectionEligibilityType", SqlDbType.VarChar, 75);
                parameters[51].Value = details.ProtectionEligibilityType;

                parameters[52] = new SqlParameter("@Invnum", SqlDbType.VarChar, 50);
                parameters[52].Value = details.Invnum;

                parameters[53] = new SqlParameter("@Custom", SqlDbType.VarChar, 50);
                parameters[53].Value = details.Custom;

                parameters[54] = new SqlParameter("@Note", SqlDbType.VarChar, 255);
                parameters[54].Value = details.Note;

                parameters[55] = new SqlParameter("@SalesTax", SqlDbType.VarChar, 50);
                parameters[55].Value = details.SalesTax;

                parameters[56] = new SqlParameter("@BuyerID", SqlDbType.VarChar, 50);
                parameters[56].Value = details.BuyerID;

                parameters[57] = new SqlParameter("@ClosingDate", SqlDbType.VarChar, 50);
                parameters[57].Value = details.ClosingDate;

                parameters[58] = new SqlParameter("@MultiItem", SqlDbType.VarChar, 50);
                parameters[58].Value = details.MultiItem;

                parameters[59] = new SqlParameter("@Period", SqlDbType.VarChar, 50);
                parameters[59].Value = DBNull.Value;

                parameters[60] = new SqlParameter("@ItemTitle", SqlDbType.VarChar, 255);
                parameters[60].Value = details.PaymentItems == null ? null : details.PaymentItems[0].Name;

                parameters[61] = new SqlParameter("@ItemID", SqlDbType.VarChar, 20);
                parameters[61].Value = details.PaymentItems == null ? null : details.PaymentItems[0].Number;

                parameters[62] = new SqlParameter("@ShippingAMT", SqlDbType.Float, 1);
                parameters[62].Value = details.PaymentItems == null ? null : details.PaymentItems[0].ShippingAMT;

                parameters[63] = new SqlParameter("@HandlingAMT", SqlDbType.Float, 1);
                parameters[63].Value = details.PaymentItems == null ? null : details.PaymentItems[0].HandlingAMT;

                parameters[64] = new SqlParameter("@ImportTime", SqlDbType.DateTime, 1);
                parameters[64].Value = DateTime.Now;

                parameters[65] = new SqlParameter("@ShippingAndHandlingAMT", SqlDbType.Float, 1);
                parameters[65].Value = details.ShippingAndHandlingAMT;

                parameters[66] = new SqlParameter("@Account", SqlDbType.VarChar, 10);
                parameters[66].Value = details.Usercode;

                SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.Text, SqlUpdateOrInsertPayPalTransactions, parameters);
            }

            return true;
        }

        private const String SqlUpdateOrInsertPayPalTransactions = @"
IF NOT EXISTS(SELECT 1 FROM PayPalTransactions WHERE TransactionID = @TransactionID)
    INSERT PayPalTransactions(
            TransactionTime
            , TimeZone
            , Name
            , Email
            , TransactionID
            , TransactionStatus
            , TransactionType
            , AMT
            , FeeAMT
            , NetAMT
            , ShippingCalculationMode
            , InsuranceOptionSelected
            , GiftMessage
		    , GiftReceiptenable
		    , GiftWrapName
		    , BuyerMarketingEmail
		    , SurveyQuestion
		    , ReceiverBusiness
		    , ReceiverEmail
		    , ReceiverID
            , DetailTransactionType
		    , PayerID
		    , PayerStatus
		    , CountryCode
		    , PayerBusiness
		    , Salutation
		    , FirstName
		    , MiddleName
		    , LastName
		    , Suffix
		    , AddressOwner
		    , AddressStatus
		    , ShipToName
		    , ShipToStreet
		    , ShipToStreet2
		    , ShipToCity
		    , ShipToState
		    , ShipToZip
		    , ShipToCountryCode
		    , ShipToPhonenum
		    , ParentTransactionID
		    , ReceiptID
		    , PaymentType
		    , OrderTime
		    , CurrencyCode
		    , SettleAMT
		    , TaxAMT
		    , PaymentStatus
		    , PendingReason
		    , ReasonCode
		    , ProtectionEligibility
		    , ProtectionEligibilityType
		    , Invnum
		    , Custom
		    , Note
		    , SalesTax
		    , BuyerID
		    , ClosingDate
		    , MultiItem
		    , Period
            , ItemTitle
            , ItemID
            , ShippingAMT
            , HandlingAMT
            , ImportTime
            , ShippingAndHandlingAMT
            , Account
		    )
        VALUES(
            @TimeStamp
            , @TimeZone
            , @Name
            , @Email
            , @TransactionID
            , @Status
            , @TransactionType
            , @AMT
            , @FeeAMT
            , @NetAMT
            , @ShippingCalculationMode
            , @InsuranceOptionSelected
		    , @GiftMessage
            , @GiftReceiptenable
		    , @GiftWrapName
		    , @BuyerMarketingEmail
		    , @SurveyQuestion
		    , @ReceiverBusiness
		    , @ReceiverEmail
		    , @ReceiverID
            , @DetailTransactionType
		    , @PayerID
		    , @PayerStatus
		    , @CountryCode
		    , @PayerBusiness
		    , @Salutation
		    , @FirstName
		    , @MiddleName
		    , @LastName
		    , @Suffix
		    , @AddressOwner
		    , @AddressStatus
		    , @ShipToName
		    , @ShipToStreet
		    , @ShipToStreet2
		    , @ShipToCity
		    , @ShipToState
		    , @ShipToZip
		    , @ShipToCountryCode
		    , @ShipToPhonenum
		    , @ParentTransactionID
		    , @ReceiptID
		    , @PaymentType
		    , @OrderTime
		    , @CurrencyCode
		    , @SettleAMT
		    , @TaxAMT
		    , @PaymentStatus
		    , @PendingReason
		    , @ReasonCode
		    , @ProtectionEligibility
		    , @ProtectionEligibilityType
		    , @Invnum
		    , @Custom
		    , @Note
		    , @SalesTax
		    , @BuyerID
		    , @ClosingDate
		    , @MultiItem
		    , @Period
            , @ItemTitle
            , @ItemID
            , @ShippingAMT
            , @HandlingAMT
            , @ImportTime
            , @ShippingAndHandlingAMT
            , @Account)
ELSE
        UPDATE PayPalTransactions SET 
            TransactionTime = @TimeStamp
            , TimeZone = @TimeZone
            , Name = @Name
            , Email = @Email
            , TransactionStatus = @Status
            , TransactionType = @TransactionType
            , AMT = @AMT
            , FeeAMT = @FeeAMT
            , NetAMT = @NetAMT
            , ShippingCalculationMode = @ShippingCalculationMode
            , InsuranceOptionSelected = @InsuranceOptionSelected
            , GiftMessage = @GiftMessage
	        , GiftReceiptenable = @GiftReceiptenable
	        , GiftWrapName = @GiftWrapName
	        , BuyerMarketingEmail = @BuyerMarketingEmail
	        , SurveyQuestion = @SurveyQuestion
	        , ReceiverBusiness = @ReceiverBusiness
	        , ReceiverEmail = @ReceiverEmail
	        , ReceiverID = @ReceiverID
            , DetailTransactionType = @DetailTransactionType
	        , PayerID = @PayerID
	        , PayerStatus = @PayerStatus
	        , CountryCode = @CountryCode
	        , PayerBusiness = @PayerBusiness
	        , Salutation = @Salutation
	        , FirstName = @FirstName
	        , MiddleName = @MiddleName
	        , LastName = @LastName
	        , Suffix = @Suffix
	        , AddressOwner = @AddressOwner
	        , AddressStatus = @AddressStatus
	        , ShipToName = @ShipToName
	        , ShipToStreet = @ShipToStreet
	        , ShipToStreet2 = @ShipToStreet2
	        , ShipToCity = @ShipToCity
	        , ShipToState = @ShipToState
	        , ShipToZip = @ShipToZip
	        , ShipToCountryCode = @ShipToCountryCode
	        , ShipToPhonenum = @ShipToPhonenum
	        , ParentTransactionID = @ParentTransactionID
	        , ReceiptID = @ReceiptID
	        , PaymentType = @PaymentType
	        , OrderTime = @OrderTime
	        , CurrencyCode = @CurrencyCode
	        , SettleAMT = @SettleAMT
	        , TaxAMT = @TaxAMT
	        , PaymentStatus = @PaymentStatus
	        , PendingReason = @PendingReason
	        , ReasonCode = @ReasonCode
	        , ProtectionEligibility = @ProtectionEligibility
	        , ProtectionEligibilityType = @ProtectionEligibilityType
	        , Invnum = @Invnum
	        , Custom = @Custom
	        , Note = @Note
	        , SalesTax = @SalesTax
	        , BuyerID = @BuyerID
	        , ClosingDate = @ClosingDate
	        , MultiItem = @MultiItem
	        , Period = @Period
            , ItemTitle = @ItemTitle
            , ItemID = @ItemID
            , ShippingAMT = @ShippingAMT
            , HandlingAMT = @HandlingAMT
            , ImportTime = @ImportTime
            , ShippingAndHandlingAMT = @ShippingAndHandlingAMT
        WHERE TransactionID = @TransactionID AND Account = @Account

UPDATE EbaySelling SET PayPalDownload = 1 WHERE PayPalTransactionID = @TransactionID";
    }
}
